﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;

namespace SaludOcupacional.DataAccess
{
    public sealed class DatabaseManager : IDisposable
    {
        // Fields definition:
        private SqlConnection connection; // Database connection
        private string connectionString; // Database string connection

        // Properties
        public SqlCommand Command
        {
            get;
            set;
        }

        // Constructor
        public DatabaseManager()
        {
            connectionString = "Data Source= .\\SQLEXPRESS;Initial Catalog=SaludOcupacional;Integrated Security=True";

            try
            {
                connection = new SqlConnection(connectionString);
            }
            catch
            {
                throw;
            }

            Command = connection.CreateCommand();
            Command.CommandText = "SELECT * FROM [Usuario]"; // Default Command
        } // end UserDataAccess constructor

        // Executes a Query in the database
        public DataSet Query()
        {
            using (DataSet resp = new DataSet())
            {
                resp.Locale = CultureInfo.CurrentUICulture;

                try
                {
                    connection.Open();

                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
                    {
                        dataAdapter.SelectCommand = Command;
                        dataAdapter.Fill(resp);
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed)
                        connection.Close();

                    Command = connection.CreateCommand();
                    Command.CommandText = "SELECT * FROM [Usuario]"; // Default Command
                }

                return resp;
            }
        } // end method Query

        // Executes a NonQuery into database
        public int NonQuery()
        {
            int value = -1;

            try
            {
                connection.Open();
                value = Command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }

                Command = connection.CreateCommand();
                Command.CommandText = "SELECT * FROM [Usuario]"; // Default Command
            }

            return value;
        } // end method NonQuery
        
        // Dispose method
        public void Dispose()
        {
            connection.Dispose();
        } // end method Dispose
    } // end class DataBaseManager
}
